import os
import time
from xlsxwriter import Workbook
from .getConfig import getConfig, logger
from .download_img import downloadImage
parent_save_path = getConfig().get('excel', 'save_path')
headers = ['IMG', 'productName', 'productPrice', 'productMore', 'productUrl']


class GenerateExcelFile:
    def __init__(self):
        self.sheet = None
        self.sheet_format = None
        self.new_file = None
        self.file_path = None

    def __init_excel(self, file_path):
        # create excel file
        self.file_path = file_path
        self.new_file = Workbook(self.file_path)
        self.sheet = self.new_file.add_worksheet("product")
        self.sheet_format = self.new_file.add_format()
        self.sheet_format.set_text_wrap()

        self.sheet.set_column(0, 0, 28)
        self.sheet.set_column(1, 4, 60)
        self.sheet.set_column(2, 2, 15)
        # set headers
        self.sheet.set_row(0, 18)

        for seq, header in enumerate(headers):
            self.sheet.write(0, seq, header)

    def __fill_data_to_excel_file(self, data_list):
        row_num = 1
        for row_data in data_list:
            self.sheet.set_row(row_num, 250)
            img_path = downloadImage.imgs_save_path(row_data["companyName"],
                                                    row_data["category"],
                                                    row_data["productImageName"])

            self.sheet.insert_image(row_num, 0, img_path)
            for j in range(1, len(headers)):
                self.sheet.write(
                    row_num, j, row_data[headers[j]], self.sheet_format)
            row_num += 1
        self.new_file.close()
        logger.info("Generate the excel file done: %s" % self.file_path)

    @staticmethod
    def __excel_save_path(companyName, file_name=None):
        save_path = os.path.join(parent_save_path,
                                 companyName
                                 )

        if not os.path.exists(save_path):
            os.makedirs(save_path)
        if file_name:
            return os.path.join(save_path, file_name + ".xlsx")

        return save_path

    def excel_main(self, sql, company, category):
        logger.info("starting to generate excel file...")
        while True:
            if downloadImage.check_download_status(sql, company, category):
                sql_cmd = "select companyName, category, productName, productPrice, \
                              productMore, productUrl, productImageName \
                       from productList \
                       where companyName='{company}' and \
                             category='{category}' and \
                             productImageStatus='ok'\
                      ".format(company=company, category=category)

                products = sql.executor(sql_cmd)
                excel_path = self.__excel_save_path(company, category)
                self.__init_excel(excel_path)
                self.__fill_data_to_excel_file(products)
                break
            else:
                logger.info("sleeping...10s....")
                time.sleep(10)
